package com.gmail.liulijuan.DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class GoodsDao {
	public ArrayList getAllGoods() {
	ResultSet rs = null;
	Goods gd = null;
	ArrayList goodsal = new ArrayList();
	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
		Connection conn = DriverManager.getConnection(url, "", "");
		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT * FROM Goods");

		while (rs.next()) {
			gd = new Goods();
			gd.setID(rs.getInt("ID"));
			gd.setGoodsName(rs.getString("GoodsName"));
			gd.setPrice(rs.getFloat("Price"));
		}

		rs.close();

		stmt.close();
		conn.close();

	} catch (Exception ex) {
		ex.printStackTrace();
	}
	return goodsal;
}

public int InsertGoods(Goods gd){
	int result=0;
	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
		Connection conn = DriverManager.getConnection(url, "", "");
		Statement stmt = conn.createStatement();

		String sql="insert into Goods(GoodsName,Price) values('"
			+gd.getGoodsName()
			+"','"
			+gd.getPrice()
			
			+"')";
		PreparedStatement ps = conn
				.prepareStatement(sql);

		result=ps.executeUpdate();

		ps.close();

		stmt.close();
		conn.close();
	} catch (Exception ex) {
		ex.printStackTrace();
	}
	return result;
}

public int UpdataGoods(Goods gd){
	int result=0;
	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
		Connection conn = DriverManager.getConnection(url, "", "");
		Statement stmt = conn.createStatement();

		String sql="update Employee set EmployeeName=?,Password=?,Sex=?,Age=?,Role=?,Phonenumber=? where id=? ";
		PreparedStatement ps = conn
				.prepareStatement(sql);
		ps.setString(1,gd.GoodsName);
		ps.setFloat(2,gd.Price);
		

		result=ps.executeUpdate();

		ps.close();

		stmt.close();
		conn.close();
	} catch (Exception ex) {
		ex.printStackTrace();
	}
	return result;
}

}



